package jdbc.apacheDBUtils;

import jdbc.datasource.JDBCUtilByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * Created with IntelliJ IDEA
 * Description:Apache的dbutils学习
 * User:22954
 * Date:2024-05-22
 * Time:8:20
 */
public class DBUtils_ {
    /*
    在关闭connection时resultset也会被关闭,不关闭连接又占据资源
    dbutils可以将resultset获得的数据全部封装到ArrayList中
     */
    @Test
    public void QueryMany() throws SQLException {
        Connection connection = JDBCUtilByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from student where no >= ?";
        /*
        query执行sql语句,得到resultSet --封装到--> ArrayList中
        new BeanListHandler<>(Student.class) 将resultSet -> Student对象 -> 封装到ArrayList
            底层通过反射获得Student类的属性,通过无参构造器实例化,通过set方法设置对象属性----必须提供无参构造器和set方法
        params给标识符 ? 赋值
        底层会创建ResultSet和PrepareStatement,在query关闭时关闭
         */
        List<Student> list = queryRunner.query(connection, sql, new BeanListHandler<>(Student.class), 1);
        for (Student student : list) {
            System.out.println(student);
        }
        JDBCUtilByDruid.close(null, null, connection);
    }

    @Test
    public void QuerySingle() throws SQLException {
        Connection connection = JDBCUtilByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from student where no = ?";
        Student student = queryRunner.query(connection, sql, new BeanHandler<>(Student.class), 2);
        System.out.println(student);
        JDBCUtilByDruid.close(null, null, connection);
    }

    @Test
    public void QueryScalar() throws SQLException {
        Connection connection = JDBCUtilByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select `name` from student where no = ?";
        Object query = queryRunner.query(connection, sql, new ScalarHandler<>(), 3);
        System.out.println(query);
        JDBCUtilByDruid.close(null, null, connection);
    }

    @Test
    public void DML() throws SQLException {
        Connection connection = JDBCUtilByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
//        String sql = "insert into student values(null,?)";
//        String sql = "update student set `name` = ? where no = ?";
        String sql = "delete from student where no = ?";
//        int affectedRow = queryRunner.update(connection, sql, "jack");
//        int affectedRow = queryRunner.update(connection, sql, "bob",2);
        int affectedRow = queryRunner.update(connection, sql,1);
        System.out.println(affectedRow);
        JDBCUtilByDruid.close(null, null, connection);
    }
}
